﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Helper;
using CallCenter.Data.Models;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace CallCenter.Data.BizUtilities
{
   public class EmployeeUtility:BaseUtility
    {
        public EmployeeUtility(DataManager manager)
            :base(manager)
        { }

        public ReturnMessage<EmployeeGridVM> InsertToEmployee(EmployeeGridVM model)
        {
            var returnMessage = new ReturnMessage<EmployeeGridVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {          
            //if (model.hireDate.CompareTo(DateTime.MinValue) == 0)
            //   model.hireDate = DateTime.Now;
            //if (model.terminationDate.CompareTo(DateTime.MinValue) == 0)
            //    model.terminationDate = DateTime.Now.AddYears(2);

            if (String.IsNullOrEmpty(model.description))
                model.description = "";

            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            if (String.IsNullOrEmpty(model.skills))
                model.skills = "";



            int department = Convert.ToInt16(model.department);
      

            var id = this.Insert(model.contactId, model.hireDate, model.terminationDate, "", model.departmentId, model.salary, model.skills, "", model.notes);
            if (id == -1)
            {
                returnMessage.Message = "Employee can not be inserted";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                int userId = Convert.ToInt32(this.Bridge.AccountUtility.getuserIdByContactId(model.contactId));
                this.Bridge.AccountUtility.UpdateUserEmployee(userId, id);
                returnMessage.Message = "New Employee has been inserted";
                returnMessage.Status = true;


            }
            return returnMessage;
        }

        public ReturnMessage<EmployeeGridVM> UpdateEmployee(EmployeeGridVM model)
        {
            var returnMessage = new ReturnMessage<EmployeeGridVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {

            if (String.IsNullOrEmpty(model.description))
                model.description = "";

            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            if (String.IsNullOrEmpty(model.skills))
                model.skills = "";


            //if (model.hireDate.CompareTo(DateTime.MinValue) == 0)
            //    model.hireDate = DateTime.Now;
            //if (model.terminationDate.CompareTo(DateTime.MinValue) == 0)
            //    model.terminationDate = DateTime.Now.AddYears(2);


            int department = Convert.ToInt16(model.department);

            var id = this.Update(model.contactId, model.hireDate, model.terminationDate, "", model.departmentId, model.salary, model.skills, "", model.notes, model.id);
            if (id == -1)
            {
                returnMessage.Message = "Employee has been updated";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Status = true;
                returnMessage.Message = "Employee has been updated";
            }
            return returnMessage;
        }

        //assign placeholder for the values in the table to insert
        public int Insert(
            int contactId,
            Nullable<DateTime> hireDate,
           Nullable< DateTime> terminationDate,
            string postLevel,
            int department,
            decimal salary,
            string skills,
            string resumePath,
            string notes
            )
        {
            int nRtn = -1;
           string sql = "INSERT INTO ddlEmployees" +
                "(contactId, hireDate, terminationDate,  department, salary, skills, notes)" +
            "VALUES(@contactId, @hireDate, @terminationDate, @department, @salary, @skills, @notes)" +
            "SELECT @pResult =@@IDENTITY";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@hireDate", SqlDbType.SmallDateTime, 10, hireDate);
                //this.Factory.AddParameter(cmd, "@terminationDate", SqlDbType.DateTime, 10, terminationDate);
               
                this.Factory.AddParameter(cmd, "@department", SqlDbType.Int, 10, department);
                this.Factory.AddParameter(cmd, "@salary", SqlDbType.Money, 10, salary);
                this.Factory.AddParameter(cmd, "@skills", SqlDbType.NVarChar, 10, skills);
             
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);

                if ((terminationDate == null) || (terminationDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@terminationDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@terminationDate", SqlDbType.DateTime, 10, terminationDate);

                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }


            return nRtn; //retruning the data stored in the table
        }
        public int Update(
           int contactId,
         Nullable<DateTime> hireDate,
           Nullable<DateTime> terminationDate,
           string postLevel,
           int department,
           decimal salary,
           string skills,
           string resumePath,
           string notes,
            int id
           )
        {
            int nRtn = -1;
           string sql= "UPDATE ddlEmployees " +
                " SET contactId=@contactId, hireDate=@hireDate, terminationDate=@terminationDate, postLevel=@postLevel, department=@department, " +
                " salary=@salary, skills=@skills, resumePath=@resumePath, notes=@notes WHERE id=@Id ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactId);
                this.Factory.AddParameter(cmd, "@hireDate", SqlDbType.SmallDateTime, 10, hireDate);
              
                this.Factory.AddParameter(cmd, "@postLevel", SqlDbType.NVarChar, 10, postLevel);
                this.Factory.AddParameter(cmd, "@department", SqlDbType.Int, 10, department);
                this.Factory.AddParameter(cmd, "@salary", SqlDbType.Money, 10, salary);
                this.Factory.AddParameter(cmd, "@skills", SqlDbType.NVarChar, 10, skills);
                this.Factory.AddParameter(cmd, "@resumePath", SqlDbType.NVarChar, 10, resumePath);
                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 1000, notes);
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, id);


                if ((terminationDate == null) || (terminationDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@terminationDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@terminationDate", SqlDbType.DateTime, 10, terminationDate);
                return this.Factory.ExecuteNonQuery(cmd);
            }

         
        
          //  return nRtn; //retruning the data stored in the table
        }

        public int Update( bool isUser,  int userId,int id  )
        {
            int nRtn = -1;
          string sql = "UPDATE ddlEmployees " +
                " SET isUser=@isUser, userId = @userid WHERE id=@Id ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@isUser", SqlDbType.Bit, 10, isUser);
                this.Factory.AddParameter(cmd, "@userid", SqlDbType.Int, 10, userId);
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
              return  this.Factory.ExecuteNonQuery(cmd);
            }
               
           // return nRtn; //retruning the data stored in the table
        }

        public void Delete(int nInvoice) //delete the row of data using the id passed to the function
        {
           string sql = "DELETE FROM ddlEmployees " +
                " WHERE ID=@ID ";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, nInvoice);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }

        public void DeleteAll() //delete all the rows in the data.
        {
           string sql  = "DELETE FROM ddlEmployees";

            this.Factory.ExecuteNonQuery(sql);
        }
        public DataTable GetAll()
        {
           string sql = "SELECT * FROM ddlEmployees";

            return this.Factory.ExecuteTable(sql);

        }


        public DataTable GetEmployee(int contactId)
        {
            string sql = "SELECT * FROM ddlEmployees e INNER JOIN ccContacts c ON c.id = e.contactId WHERE e.contactId = " + contactId;

            return this.Factory.ExecuteTable(sql);

        }

        public List<EmployeeGridVM> GetEmp()
        {
           string sql = "SELECT e.*, c.lastName+','+c.firstName as contactname, d.description " +
                                    " as depCode,d.id as departmentId FROM ddlEmployees e " +
                                    " INNER JOIN ccContacts c on c.id = e.contactId " +
                                    " INNER JOIN ddlDepartments d on d.id = e.department";

            return this.Factory.ExecuteObjects<EmployeeGridVM>(sql);

        }

        public DataTable GetContact(int id)
        {
           string sql = "SELECT contactId FROM ddlEmployees where id = " + id;

            return this.Factory.ExecuteTable(sql);

        }

        public DataTable GetUserName()
        {
           string sql = " select ur.*, cc.lastName+' '+cc.firstName as name from UserProfile ur " +
                " inner join ddlEmployees em on em.id = ur.EmployeeId " +
                " inner join ccContacts cc on cc.id = em.contactId ";
            return this.Factory.ExecuteTable(sql);

        }

        //public DataRow GetUserNameById(int userId)
        //{
        //    string sql = " select ur.*, cc.lastName+' '+cc.firstName as name from UserProfile ur " +
        //         " inner join ddlEmployees em on em.id = ur.EmployeeId " +
        //         " inner join ccContacts cc on cc.id = em.contactId " +
        //         " where ur.userId = " + userId;

        //    return this.Factory.E(sql);

        //}


        public DataRow GetEmpIdByUser(int UserId)
        {
            //string sql = "SELECT * FROM UserProfile WHERE userId = " + UserId;

            string sql = "SELECT * FROm tbl_Users where id = " + UserId;

            return this.Factory.ExecuteRecord(sql);
        }


        public int GetcontactIdByEmployeeId(int id)
        {
            int result = 0;
            string sql = "select contactid from ddlEmployees where id = " + id;

            try
            {
                result = Convert.ToInt32(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

        }

        public List<ProductGridViewModel> GetEmployeeProjects(int ContactId)
        {
            string sql = "select distinct PR.name as projectname, PR.note as notes ,s.statusCode as statusName from ddlProductRequests as PR " +
  " Join ddlProductRequestMember as PRM on PRM.productRequestId = PR.id " +
   " Join ddlStatus as s on s.id = PR.statusId " +
  " where PRM.contactId =  " + ContactId;

            return this.Factory.ExecuteObjects<ProductGridViewModel>(sql);
        }


    }
}
